package com.xinhua.web.servlet;

import com.xinhua.domain.Consumption;
import com.xinhua.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

//条件查询
@WebServlet("/selectServlet")
public class SelectServlet extends HttpServlet {
    private QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        super.doGet(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String name = req.getParameter("name");
        System.out.println("name = " + name);
        String time = req.getParameter("time");
        String years = req.getParameter("years");
        String times = req.getParameter("times");
        int uuid = (int) req.getSession().getAttribute("uuid");
        System.out.println(years + "" + times);
        if (name.equals("") && time.equals("") && years.equals("") && times.equals("")) {
            req.getRequestDispatcher("/findServlet").forward(req, resp);
        } else {
            if (name.equals("") && time.equals("")) {
                try {
                    System.out.println(years);                      //按照年份查询         按照月份查询
                    String sql = "select * from day23.consumption where (year (time)=? or month (time)=?) and uuid = ?";
                    String newStr = years;
                    String newSte = times;
                    List<Consumption> query = queryRunner.query(sql, new BeanListHandler<Consumption>(Consumption.class), newStr, newSte, uuid);
                    System.out.println(query);
                    req.setAttribute("user", query);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                req.getRequestDispatcher("/list.jsp").forward(req, resp);
            } else {
                try {
                    String sql = "select * from day23.consumption where (name like ? or time like ?) and uuid = ?";
                    String newStr = "%" + name + "%";
                    List<Consumption> query = queryRunner.query(sql, new BeanListHandler<Consumption>(Consumption.class), newStr, time, uuid);
                    System.out.println(query);
                    req.setAttribute("user", query);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                req.getRequestDispatcher("/list.jsp").forward(req, resp);
            }

        }
    }
}
